package data;

import database.DataBaseConnection;
import entity.Loginfo;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class QueryLogData {
    private String start;
    private String end;
    private String pageSize;
    private String pageNumber;

    public QueryLogData(String start, String end, String pageSize, String pageNumber) {
        this.start = start;
        this.end = end;
        this.pageSize = pageSize;
        this.pageNumber = pageNumber;
    }

    public String query() {
        JSONObject jsonObject = new JSONObject();
        ArrayList<Loginfo> loginfos = new ArrayList<>();
        Connection connection = DataBaseConnection.getConnection();
        if (connection != null) {
            int totalNumber = 0;
            try {
                String count = "SELECT count(*) AS count " +
                        "FROM loginfo,exception " +
                        "WHERE loginfo.type = exception.id AND loginfo.time < ? AND loginfo.time > ? " +
                        "ORDER BY loginfo.id DESC";
                PreparedStatement preparedStatement1 = connection.prepareStatement(count);
                preparedStatement1.setString(1, this.end);
                preparedStatement1.setString(2, this.start);
                ResultSet resultSet = preparedStatement1.executeQuery();
                while (resultSet != null && resultSet.next()) {
                    totalNumber = resultSet.getInt("count");
                }
                String sql = "SELECT loginfo.id,loginfo.operator,loginfo.time," +
                        "loginfo.level,loginfo.information,exception.type" +
                        " FROM loginfo,exception " +
                        "WHERE loginfo.type = exception.id AND loginfo.time < ? AND loginfo.time > ? " +
                        "ORDER BY loginfo.id DESC LIMIT " + this.pageNumber + "," + this.pageSize;
                PreparedStatement preparedStatement2 = connection.prepareStatement(sql);
                preparedStatement2.setString(1, this.end);
                preparedStatement2.setString(2, this.start);
                ResultSet result = preparedStatement2.executeQuery();
                while (result != null && result.next()) {
                    long id = result.getLong("id");
                    String operator = result.getString("operator");
                    Timestamp executeTime = result.getTimestamp("time");
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String time = simpleDateFormat.format(executeTime);
                    String type = result.getString("type");
                    String level = result.getString("level");
                    String information = result.getString("information");
                    loginfos.add(new Loginfo(id, operator, time, type, level, information));
                }
                JSONArray jsonArray = JSONArray.fromObject(loginfos);
                jsonObject.put("total", totalNumber);
                jsonObject.put("rows", jsonArray);
                DataBaseConnection.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return jsonObject.toString();
    }
}
